From 0539061967946bc0a51ac165de5eaeff0052d1a2 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 31 Jul 2026 18:43:46 +0000 Subject: [PATCH] SWELLING_SOURCES min swelling cannot exceed the max swelling --- .../lib/triggers/create/cycle_states.m4 | 83 +++++++++++++++++++ .../lib/triggers/create/swelling_sources.m4 | 51 ++++++++++++ doc/src/tables/swelling_sources.m4 | 3 + 3 files changed, 137 insertions(+) diff --git a/db/schemas/lib/triggers/create/cycle_states.m4 b/db/schemas/lib/triggers/create/cycle_states.m4 index 2dcf9fb..d23b9ed 100644 --- a/db/schemas/lib/triggers/create/cycle_states.m4 +++ b/db/schemas/lib/triggers/create/cycle_states.m4 @@ -100,6 +100,89 @@ CREATE OR REPLACE FUNCTION cycle_states_func () END IF; END IF; + IF TG_OP = 'UPDATE' + AND NEW.ssrank IS DISTINCT FROM OLD.ssrank + AND NEW.ssrank IS NOT NULL THEN + -- SWELLING_SOURCES.SwellingMin cannot have a larger rank than + -- SWELLING_SOURCES.SwellingMax + + DECLARE + min_code cycle_states.code%TYPE; + min_ssrank cycle_states.ssrank%TYPE; + max_code cycle_states.code%TYPE; + max_ssrank cycle_states.ssrank%TYPE; + + BEGIN + SELECT swelling_sources.id, swelling_sources.date + , swelling_sources.source, swelling_sources.animid + , swelling_sources.swellingmin + , swelling_sources.swellingmax + , min_states.code, min_states.ssrank + , max_states.code, max_states.ssrank + INTO a_id , a_date + , a_source , a_animid + , a_swellingmin + , a_swellingmax + , min_code , min_ssrank + , max_code , max_ssrank + FROM swelling_sources + JOIN cycle_states AS min_states + ON (min_states.code + IS NOT DISTINCT FROM swelling_sources.swellingmin) + JOIN cycle_states AS max_states + ON (max_states.code + IS NOT DISTINCT FROM swelling_sources.swellingmax) + WHERE (min_states.code = NEW.code + OR max_states.code = NEW.code) + AND min_states.ssrank > max_states.ssrank + -- Ensure a consistent error message + ORDER BY swelling_sources.animid + , swelling_sources.date + , swelling_sources.source + , swelling_sources.id; -- There is no uniqueness constraint + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of CYCLE_STATES' + , DETAIL = 'The SSRank value cannot changed so that' + || ' any SWELLING_SOURCES.SwellingMin value' + || ' ranks higher than any' + || ' SWELLING_SOURCES.SwellingMax value' + || ': Key (Code) = (' + || NEW.code + || '), Value (AsNum) = (' + || textualize(`NEW.asnum') + || '), Value (SSRank) = (' + || NEW.ssrank + || '): Key (SWELLING_SOURCES.ID) = (' + || a_id + || '), Value (SWELLING_SOURCES.Source) = (' + || a_source + || '), Value (SWELLING_SOURCES.Date) = (' + || a_date + || '), Value (SWELLING_SOURCES.AnimID) = (' + || a_animid + || '), Value (SWELLING_SOURCES.SwellingMin) = (' + || textualize(`a_swellingmin') + || '), Value (SWELLING_SOURCES.SwellingMax) = (' + || textualize(`a_swellingmax') + || '): Key (CYCLE_STATES.Code) = (' + || CASE + WHEN NEW.code = min_code + THEN max_code + ELSE min_code + END + || '), Value (CYCLE_STATES.SSRank) = (' + || CASE + WHEN NEW.code = min_code + THEN max_ssrank + ELSE min_ssrank + END + || ')'; + END IF; + END; + END IF; + RETURN NULL; END; $$; diff --git a/db/schemas/lib/triggers/create/swelling_sources.m4 b/db/schemas/lib/triggers/create/swelling_sources.m4 index add30dd..f41167d 100644 --- a/db/schemas/lib/triggers/create/swelling_sources.m4 +++ b/db/schemas/lib/triggers/create/swelling_sources.m4 @@ -202,7 +202,58 @@ CREATE OR REPLACE FUNCTION swelling_sources_func () || ')'; END IF; END; + END IF; + + -- SwellingMin must not be ranked higer than SwellingMax + IF NEW.swellingmin IS NOT NULL + AND NEW.swellingmax IS NOT NULL + AND (TG_OP = 'INSERT' + OR (TG_OP = 'UPDATE' + AND (NEW.swellingmin IS DISTINCT FROM OLD.swellingmin + OR NEW.swellingmax IS DISTINCT FROM OLD.swellingmax))) + THEN + DECLARE + min_ssrank cycle_states.ssrank%TYPE; + max_ssrank cycle_states.ssrank%TYPE; + BEGIN + SELECT min_states.ssrank, max_states.ssrank + INTO min_ssrank , max_ssrank + FROM cycle_states AS min_states + , cycle_states AS max_states + WHERE min_states.code = NEW.swellingmin + AND max_states.code = NEW.swellingmax + -- We already tested above to be sure the ranks are not NULL + AND min_states.ssrank > max_states.ssrank; + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of SWELLING_SOURCES' + , DETAIL = 'Invalid SwellingMin/SwellingMax combination' + || '; the CYCLE_STATES.SSRank of the SwellingMax' + || ' value cannot be less than that of the' + || E' SwellingMin value\'s' + || ': Key (ID) = (' + || NEW.id + || '), Value (AnimID) = (' + || NEW.animid + || '), Value (Date) = (' + || NEW.date + || '), Value (SwellingMin) = (' + || NEW.swellingmin + || '), Value (SwellingMax) = (' + || NEW.swellingmax + || '): Key (CYCLE_STATES.Code) = (' + || NEW.swellingmin + || '), Value (CYCLE_STATES.SSRank) = (' + || min_ssrank + || '): Key (CYCLE_STATES.Code) = (' + || NEW.swellingmax + || '), Value (CYCLE_STATES.SSRank) = (' + || max_ssrank + || ')'; + END IF; + END; END IF; RETURN NULL; diff --git a/doc/src/tables/swelling_sources.m4 b/doc/src/tables/swelling_sources.m4 index 0d02a51..2e09988 100644 --- a/doc/src/tables/swelling_sources.m4 +++ b/doc/src/tables/swelling_sources.m4 @@ -69,6 +69,9 @@ This means that the |SWELLING_SOURCES.SwellingMin| and |CYCLE_STATES.Code| values where the |CYCLE_STATES|.\ |CYCLE_STATES.SSRank| column is non-|null|. +The |SWELLING_SOURCES.SwellingMin| value must be, according to the +|CYCLE_STATES|.\ |CYCLE_STATES.SSRank| ranking, less than or equal to +the |SWELLING_SOURCES.SwellingMax| ranking. .. contents:: :depth: 2 -- 2.34.1